package cn.sypt.yuhao.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.jdbc.TxQueryRunner;
import cn.sypt.yuhao.domain.Acategory;
import cn.sypt.yuhao.domain.Bcategory;
import cn.sypt.yuhao.domain.Category;
import cn.sypt.yuhao.domain.Comments;
import cn.sypt.yuhao.domain.Exam;
import cn.sypt.yuhao.domain.Reply;
import cn.sypt.yuhao.domain.Tip;
import cn.sypt.yuhao.domain.User;
import cn.sypt.yuhao.domain.Video;
import cn.sypt.yuhao.domain.Words;
import cn.sypt.yuhao.domain.Zan;
import cn.sypt.yuhao.model.Tool;
import cn.sypt.yuhao.pager.PageBean;
import cn.sypt.yuhao.pager.PageConstants;

public class VideoDao {
	private QueryRunner qr = new TxQueryRunner();

	public List<Tip> tip(String spname){
		String sql="select spname title from video where spname like '%"+spname+"%'";
	//	System.out.println("houtai:"+spname);
		try {
			List<Tip> list=qr.query(sql, new BeanListHandler<Tip>(Tip.class));
		//	System.out.println(list.isEmpty());
			return list;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	
	
	/**
	 * 是否收藏
	 * 
	 * @param spid
	 * @return
	 */

	public Zan findBysc(String spid,String uid) {
		String sql = "select * from zan where spid=? and uid=?";
		// System.out.println("spid==="+spid);
		try {
			Zan zan = qr.query(sql, new BeanHandler<Zan>(Zan.class), spid,uid);
			return zan;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	public List<Category> yijifenlei(){
		String sql="select * from category order by fnum asc";
		try {
			List<Category> listcategory=qr.query(sql, new BeanListHandler<Category>(Category.class));
			return listcategory;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	public List<Acategory> erjifenlei(String fid){
		String sql="select * from acategory where fid=?";
		try {
			List<Acategory> listacategory=qr.query(sql, new BeanListHandler<Acategory>(Acategory.class),fid);
			return listacategory;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	public List<Bcategory> sanjifenlei(String aid){
		String sql="select * from bcategory where aid=?";
		try {
			List<Bcategory> listbcategory=qr.query(sql, new BeanListHandler<Bcategory>(Bcategory.class),aid);
			return listbcategory;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	
	

	public List<Video> findtuijian(String uid) {
	
	//	System.out.println("uid"+uid);
		try {
		String sql1="select MAX(ztime) from zan where uid=?";
		String ztime=(String) qr.query(sql1, new ScalarHandler(),uid);
	//	System.out.println("ztime"+ztime);
		String sql2="select spid from zan where uid=? and ztime=?";
		String spid=(String) qr.query(sql2, new ScalarHandler(),uid,ztime);
	//	System.out.println("spid"+spid);
		String sql3="select bid from video where spid=?";
		String bid=(String) qr.query(sql3, new ScalarHandler(),spid);
	//	System.out.println("bid"+bid);
		String sql4="select * from video where bid=? ORDER BY spdz desc LIMIT 6";
			List<Video> listVideo=qr.query(sql4, new BeanListHandler<Video>(Video.class),bid);
	//		System.out.println(listVideo);
			return listVideo;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public Exam findByExam(String spid,int n){
	
		String sql="select * from exam where spid=? limit ?,1";
		try {
			Exam exam=qr.query(sql, new BeanHandler<Exam>(Exam.class),spid,n);
			return exam;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		
		
	}
	
	

	/**
	 * 最热排行
	 * 
	 * @param bid
	 * @return
	 */
	public Video findByspid(String spid, String uid1) {
		String sql1 = "select * from video where spid=?";
		String sql2 = "select * from comment where spid=? order by ptime desc";
		String sql4 = "select * from zan where spid=? and uid=?";
		try {
			Video vid = qr.query(sql1, new BeanHandler<Video>(Video.class),
					spid);
			List<Comments> comlist = qr.query(sql2,
					new BeanListHandler<Comments>(Comments.class), spid);
			//过滤词汇
			
			
			for (int i = 0; i < comlist.size(); i++) {
				String uid = comlist.get(i).getUid();
				String sql3 = "select * from user where uid=?";
				User user = qr.query(sql3, new BeanHandler<User>(User.class),
						uid);
				comlist.get(i).setUser(user);
				
				String sql5="select count(*) from reply where cid=?";
				Number number=(Number) qr.query(sql5, new ScalarHandler(),comlist.get(i).getCid());
				comlist.get(i).setCount(number.intValue());
			}
			if (uid1 == null) {
				uid1 = " ";
			}
			Zan zan = qr.query(sql4, new BeanHandler<Zan>(Zan.class), spid,
					uid1);
			vid.setZan(zan);
			vid.setComlist(comlist);
		//	System.out.println("文档路径：" + vid.getSpwdurl());
			return vid;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public List<Video> findByhot() {
		String sql = "select * from video where spsh=1 order by spdz desc limit 6";
		try {
			List<Video> listVideo = qr.query(sql, new BeanListHandler<Video>(
					Video.class));
			return listVideo;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 最新排行
	 * 
	 * @return
	 */
	public List<Video> findBynew() {
		String sql = "select * from video where spsh=1 order by sptime desc limit 6";
		try {
			List<Video> listVideo = qr.query(sql, new BeanListHandler<Video>(
					Video.class));
			return listVideo;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 下载量排行
	 * 
	 * @return
	 */
	public List<Video> findBydow() {
		String sql = "select * from video where spsh=1 order by spxz desc limit 6";
		try {
			List<Video> listVideo = qr.query(sql, new BeanListHandler<Video>(
					Video.class));
			return listVideo;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 查找一类视频（力学）
	 * 
	 * @param pc
	 * @param bid
	 * @return
	 */
	public PageBean<Video> findBybid(int pc, String bid) {
		int ps = PageConstants.VIDEO_PAGE_SIZE;// 每页记录数
		String sql = "select * from video where bid=? and spsh=1 order by sptime desc limit ?,?";
		try {
			String sql1 = "select count(*) from video where bid=? and spsh=1";
			Number number = (Number) qr.query(sql1, new ScalarHandler(),bid);
			int tr = number.intValue();// 得到了总记录数
			List<Video> listvideo = qr.query(sql, new BeanListHandler<Video>(
					Video.class), bid, (pc - 1) * ps, ps);
			PageBean<Video> pb = new PageBean<Video>();
			/*
			 * 其中PageBean没有url，这个任务由Servlet完成
			 */
		//	int tr=listvideo.size();
			pb.setBeanList(listvideo);
			pb.setPc(pc);
			pb.setPs(ps);
			pb.setTr(tr);

			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 查找一个大学科（物理）
	 * 
	 * @param pc
	 * @param fid
	 * @return
	 */

	public PageBean<Video> findByfid(int pc, String fid) {
		int ps = PageConstants.VIDEO_PAGE_SIZE;// 每页记录数
		String sql = "select * from video where fid=? and spsh=1 order by sptime desc limit ?,?";
		try {
			String sql1 = "select count(*) from video where fid=? and spsh=1";
			Number number = (Number) qr.query(sql1, new ScalarHandler(),fid);
			int tr = number.intValue();// 得到了总记录数
			List<Video> listvideo = qr.query(sql, new BeanListHandler<Video>(
					Video.class), fid, (pc - 1) * ps, ps);
			PageBean<Video> pb = new PageBean<Video>();
			/*
			 * 其中PageBean没有url，这个任务由Servlet完成
			 */
		//	int tr=listvideo.size();
			pb.setBeanList(listvideo);
			pb.setPc(pc);
			pb.setPs(ps);
			pb.setTr(tr);

			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/*
	 * 点赞功能
	 */
	public int dianzan(String uid, String spid, String ztime, int value) {
		String sql1 = "insert into zan values(?,?,?)";
		String sql2 = "update video set spdz=spdz+? where spid=?";
		String sql3 = "select spdz from video where spid=?";
		try {
			qr.update(sql1, uid, spid, ztime);
			qr.update(sql2, value, spid);
			int spdz = (Integer) qr.query(sql3, new ScalarHandler(), spid);
			return spdz;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/*
	 * 更新下载数
	 */
	public int updatedown(String spid) {
		String sql = "select spxz from video where spid=?";
		try {
			int spxz = (Integer) qr.query(sql, new ScalarHandler(), spid);
			return spxz;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 下载功能
	 */

	public void download(String uid, String spid, String xztime) {
		String sql1 = "insert into download values(?,?,?)";
		String sql2 = "update video set spxz=spxz+1 where spid=?";
		// String sql3="select spxz from video where spid=?";
		try {
			qr.update(sql1, uid, spid, xztime);
			qr.update(sql2, spid);
			// int spxz=(Integer) qr.query(sql3, new ScalarHandler(),spid);
			// return spxz;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void addcomment(Comments comment) {
		String sql = "insert into comment values(?,?,?,?,?,?)";
		///过滤敏感词汇
		List<Words> listwords=findWords();
			String pstr=comment.getPstr();
			for (int j = 0; j < listwords.size(); j++) {
				if(pstr.contains(listwords.get(j).getWords())){
					pstr=pstr.replaceAll(listwords.get(j).getWords(), "***");
				}
			}
			comment.setPstr(pstr);
			comment.setCount(0);
		try {
			qr.update(sql, comment.getUid(), comment.getSpid(),
					comment.getPtime(), comment.getPstr(), comment.getCid(),comment.getCount());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public List<Reply> findReply(String cid,int count) {
		String sql = "select * from reply where cid=? order by rtime desc";
		String sql1 = "select * from user where uid=?";
		if(count==0){
			String sql2="update comment set count=? where cid=?";
			try {
				qr.update(sql2,count,cid);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		try {
			List<Reply> listReply = qr.query(sql, new BeanListHandler<Reply>(
					Reply.class), cid);
			for (int i = 0; i < listReply.size(); i++) {
				User user = qr.query(sql1, new BeanHandler<User>(User.class),
						listReply.get(i).getUid());
				listReply.get(i).setUser(user);
			}
			return listReply;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void addReply(Reply reply) {
		String sql = "insert into reply values(?,?,?,?,?)";
		String sql1="update comment set count=count+1 where cid=?";
		List<Words> listwords=findWords();
		String rstr=reply.getRstr();
		for (int j = 0; j < listwords.size(); j++) {
			if(rstr.contains(listwords.get(j).getWords())){
				rstr=rstr.replaceAll(listwords.get(j).getWords(), "***");
			}
		}
		reply.setRstr(rstr);
		
		try {
			qr.update(sql1,reply.getCid());
			Object params[]={reply.getRid(), reply.getCid(), reply.getUid(),
					reply.getRstr(), reply.getRtime()};
			qr.update(sql,params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public List<Video> search(String spname) {
		String sql = "select * from video where spsh=1 and spname like '%" + spname
				+ "%' order by sptime desc ";
		try {
			List<Video> listvideo = qr.query(sql, new BeanListHandler<Video>(
					Video.class));

			return listvideo;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public Bcategory findBybname(String bname) throws SQLException {
		String sql = "select * from bcategory where bname=?";
		Bcategory bcategory = (Bcategory) qr.query(sql,
				new BeanHandler<Bcategory>(Bcategory.class), bname);
		return bcategory;
	}

	public String findBybid(String aid) throws SQLException {
		String sql = "select fid from acategory where aid=?";
		String fid = (String) qr.query(sql, new ScalarHandler(), aid);
		return fid;
	}

	public void addVideo(Video video) throws SQLException {
		String sql = "insert video(spid,spname,spstr,sptime,spurl,sptpurl,bid,fid,uname,spwdurl) values(?,?,?,?,?,?,?,?,?,?)";
		Object[] params = { video.getSpid(), video.getSpname(),
				video.getSpstr(), video.getSptime(), video.getSpurl(),
				video.getSptpurl(), video.getBid(), video.getFid(),
				video.getUname(), video.getSpwdurl() };
		qr.update(sql, params);

	}

	/**
	 * 查找单个评论
	 * 
	 * @param cid
	 * @return
	 */
	public Comments findBycid(String cid) {
		String sql = "select * from comment where cid=?";
		String sql1 = "select * from user where uid=?";
		try {
			Comments comment = qr.query(sql, new BeanHandler<Comments>(
					Comments.class), cid);
			User user = qr.query(sql1, new BeanHandler<User>(User.class),
					comment.getUid());
			comment.setUser(user);
			return comment;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public Video findByadminspid(String spid) {
		String sql = "select * from video where spid=?";
		try {
			Video video = qr.query(sql, new BeanHandler<Video>(Video.class),
					spid);
			return video;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
/**
 * 查找个人主页评论
 * @param uid
 * @return
 */
	public List<Comments> findComment(String uid){
		String sql="select * from comment where uid=?";
		try {
			List<Comments> listComment=qr.query(sql, new BeanListHandler<Comments>(Comments.class),uid);
			String sql1="select * from video where spid=?";
			for (int i = 0; i < listComment.size(); i++) {
				String spid=listComment.get(i).getSpid();
				Video video=qr.query(sql1, new BeanHandler<Video>(Video.class),spid);
				listComment.get(i).setVideo(video);
				listComment.get(i).setPstr(Tool.getSub(25, listComment.get(i).getPstr()));
				;
			}
			return listComment;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

public List<Words> findWords() {
	String sql="select * from words";
	try {
		List<Words> words=qr.query(sql, new BeanListHandler<Words>(Words.class));
		return words;
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
	
}

}
